set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode = 200;
set hive.exec.max.dynamic.partitions = 200;
-- **************************************************************************
--    Project Name:   末端监控7日内0出库驿站数-汇总
--    Job Name:  末端监控7日内0出库驿站数-汇总
--    Description : 从末端代派明细中进行汇总7日内0出库驿站数 剔除入库小于100的
--    Author :        郭瑞玲
--    date：          2022/07/10
-- **************************************************************************
--    Relation ：
--    jms_dm.dm_end_dispatch_saven_zero_out_cnt_dt << [
--     jms_dm.dm_network_end_dispatch_diff_detail
--     jms_dim.dim_network_whole_massage
--    ]
-- **************************************************************************
--    modify by 2022-08-26 修改代码
--              2022-09-14 添加品牌id字段
-- **************************************************************************
with tmp as (
select                   --明细数据的粒度是网点、门店、品牌
update_time,
scansitecode,
store_code,
max(store_name) store_name,
sum(store_in_total) store_in_total,       --门店入库量
sum(seven_store_out_total) seven_store_out_total, --门店7日出库量
business_id,
max(business_name) business_name
from jms_dm.dm_network_end_dispatch_diff_rate
where dt between date_add('{{ execution_date | cst_ds }}',-6) and '{{ execution_date | cst_ds }}'
group by
scansitecode,store_code,update_time,business_id
)

insert overwrite table jms_dm.dm_end_dispatch_saven_zero_out_cnt_dt partition(dt)
select
t.update_time,
net.manage_code,
net.manage_name,
net.agent_code,
net.agent_name,
net.fran_code as franchisee_code,
net.fran_name as franchisee_name,
t.scansitecode as network_code,
net.name as network_name,
t.store_code,
t.store_name,
t.store_in_total,
---count(1) over(partition by t.update_time,t.scansitecode)  store_cnt_0,  --需求着这样按照网点汇总
count(1) over(partition by t.update_time,t.scansitecode,t.store_code)  store_cnt_0, --和后端联调后的结果：按照门店汇总
t.business_id,
t.business_name,
t.update_time as dt
from
(
select                   --明细数据的粒度是网点、门店、品牌
update_time,
scansitecode,
store_code,
max(store_name) store_name,
store_in_total,       --门店入库量
seven_store_out_total, --门店7日出库量
business_id,
max(business_name) business_name
from tmp
where seven_store_out_total=0
and store_in_total>=100
group by
scansitecode,store_code,store_in_total,seven_store_out_total,update_time,business_id
)t
left join jms_dim.dim_network_whole_massage net on net.code=t.scansitecode
distribute by 1;